﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data.SqlClient;
using System.Data;
using System.Web.Script.Serialization;
namespace Automobile.service.DAL
{
  public  class XiaoShowDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 根据年份区间获取采购信息
        /// </summary>
        /// <param name="firstyear"></param>
        /// <param name="lastyear"></param>
        /// <returns></returns>
        public string CaigouByyear(string firstyear, string lastyear)
        {
            StringBuilder builder = new StringBuilder("select year(Shipments_data) Purchase_Date,sum(Shipments_Pricepor) SumMoney from Shipments where YEAR(Shipments_data) between " + firstyear + "  and  " + lastyear + " group by year(Shipments_data)");
            DataTable table = helper.ExecDataTable(builder.ToString());
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
            Dictionary<string, object> childRow;
            foreach (DataRow row in table.Rows)
            {
                childRow = new Dictionary<string, object>();
                foreach (DataColumn col in table.Columns)
                {
                    childRow.Add(col.ColumnName, row[col]);
                }
                parentRow.Add(childRow);
            }
            return jsSerializer.Serialize(parentRow);
        }

        /// <summary>
        /// 根据月份获取采购信息
        /// </summary>
        /// <param name="firstyear"></param>
        /// <param name="lastyear"></param>
        /// <returns></returns>
        public string CaigouByMonth(string firstyear, string firstmonth, string lastmonth)
        {
            StringBuilder builder = new StringBuilder("select month(Shipments_data) Purchase_Date,sum(Shipments_Pricepor) SumMoney from Shipments where month(Shipments_data) between " + firstmonth + " and " + lastmonth + "  group by month(Shipments_data),year(Shipments_data)  having year(Shipments_data)='" + firstyear + "'");
            DataTable table = helper.ExecDataTable(builder.ToString());
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
            Dictionary<string, object> childRow;
            foreach (DataRow row in table.Rows)
            {
                childRow = new Dictionary<string, object>();
                foreach (DataColumn col in table.Columns)
                {
                    childRow.Add(col.ColumnName, row[col]);
                }
                parentRow.Add(childRow);
            }
            return jsSerializer.Serialize(parentRow);
        }
    }
}
